package idv.arthur.work.validation;

import idv.arthur.work.Widgets;
import idv.arthur.work.strucs.S5;
import idv.arthur.work.strucs.ValHisStruc;
import idv.arthur.work.strucs.ValHistory;
import idv.arthur.work.strucs.ValMultiHisStruc;

import java.io.IOException;
import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.google.gson.stream.JsonReader;

/**
 *		創建時間：2013/2/21 下午5:31:30
 *		創建人員：郭啟揚 (842297)
 *		
 *		一支程式要部署到指定的主機上去前
 *
 *		此servlet檢查 部署的目的地(主機、部署路徑、延伸路徑)上，最後的部署記錄中
 *		是否存在比目前所部署檔案更新的版本
 *		若存在，則出示訊息警告
 */
public class ValidateHistory extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
    public ValidateHistory() {
        super();
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    	doPost(request,response);
    }

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/json");
		response.setCharacterEncoding( wid.UIcodeSet );		
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;	
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		String errPars = "}";

		
		boolean isnonweb;																	//分辨該項次的執行方式	是否為 nonweb
		String type = request.getParameter("type");						// Batch為批次( 以部署計畫代碼來部署 )，Single是單檔， Manual為手動部署
		final String bssId = request.getParameter("bssId");					// 系統
		String subSysId = request.getParameter("subSysId");		// 子系統
		String envId = request.getParameter("envId");					// 環境
		String[] hsLists = null;
		
		String strSQL = "SELECT squadpath FROM squad WHERE bssid=? AND subsysid=? AND squadid=?";
		 
		ValHistory vmhses = new ValHistory(); 
		ArrayList<ValMultiHisStruc> vmNonWebs = new ArrayList<ValMultiHisStruc>();
		ArrayList<ValMultiHisStruc> vmWebs = new ArrayList<ValMultiHisStruc>();
		
		int filePortfolio = Integer.parseInt( request.getParameter("filePortfolio") );	//這次部署的檔案組合，1為有web和nonweb，2為僅有nonweb，3為僅有web
		
		try {
			conn = wid.getConn(true);
			
			errPars = 
					wid.toJ("bssid", bssId)+
					wid.toJend("subsysid", subSysId);
			
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, bssId);
			ps.setString(2, subSysId);
			
			if ( "Single".equalsIgnoreCase(type) ) {		//單檔重新部署
				/*
				 * 因為是 single, 單一個檔案的部署
				 * 所以filePortfolio 僅會有 2 或 3 的結果
				 **/
				ValMultiHisStruc vmhs = new ValMultiHisStruc();
				/*	此時已有了所有要去的資源群組了	*/
				vmhs.setFileName( request.getParameter("fileName") );													//檔案名稱
				vmhs.setFileVer( Integer.parseInt(request.getParameter("ver")) );									//檔案版次

				ArrayList<ValHisStruc> v1s = new ArrayList<ValHisStruc>();										//這個路徑組合下要去的資源群組
				
				if ( filePortfolio==2 ) {	//	2為僅有nonweb
					vmhs.setFilePath( request.getParameter("filePath") );													//檔案路徑
					hsLists = request.getParameter("hsLists").substring(1, request.getParameter("hsLists").length() ).split( wid.splitTokenTHREE );						//資源群組列表 (原始字串，尚需整理)					
					/* 將資源群組下的主機，從字串中解析出來	*/
					for (int i=0; i < hsLists.length ; i++ ) {																				//hsList中是所有要去的資源群組
						ValHisStruc v1 = new ValHisStruc();																				//記錄某個資源群組																		
						v1.setSquadId( hsLists[i].substring( 0, hsLists[i].indexOf( wid.splitTokenFOUR ) ) );	//資源群組ID
						errPars = 
								wid.toJ("bssid", bssId)+
								wid.toJ("subsysid", subSysId) +
								wid.toJend("squadid", v1.getSquadId() );
						
						//wid.bugTrace( conn, ps,errPars, wid.toEType("Bug Trace") );
						
						ps.setString(3,v1.getSquadId() );
						rs = ps.executeQuery();
						if ( rs.next() ) {	//資源群組路徑 ( 因為是nonWeb的方式，資源群組路徑和部署路徑都要考慮 )
							v1.setSquadPath(rs.getString("squadpath"));																
						} else {
							v1.setSquadPath("");
						}
						
						hsLists[i] = hsLists[i].substring(wid.splitTokenTWO.length()+wid.splitTokenFOUR.length()+v1.getSquadId().length(), hsLists[i].length());
						String[] hsPars = hsLists[i].split(wid.splitTokenTWO);
						ArrayList<S5> strcs = new ArrayList<S5>(); 
						for (int j=0;j < hsPars.length;j++) {
							S5 strc = new S5();
							strc.setA( hsPars[j].split( wid.splitTokenONE)[2] );		//主機名稱
							strc.setB( hsPars[j].split( wid.splitTokenONE)[0] );		//主機IP
							strc.setC( hsPars[j].split( wid.splitTokenONE)[1] );		//主機Port
							strcs.add( strc );
						}
						v1.setLaos(strcs);		//這個資源群組，要被部署去這些主機 ( 那些主機的資訊是從 "ValidateHost" 提供的 )
						v1s.add(v1);
					}
					vmhs.setVhses( v1s );	//所有要去的資源群組，和它相對要被部署的主機及路徑
					
					vmNonWebs.add( vmhs );
					vmhses.setNonWeb( vmNonWebs );
				} else {		//	3 為僅有web
					hsLists = request.getParameter("webSQ").substring(0, request.getParameter("webSQ").length() ).split( wid.splitTokenONE );						//資源群組列表 (原始字串，尚需整理)
					
					strSQL ="SELECT t1.webconsoleip FROM wcl t1,env t2 WHERE t1.bssid=? AND t1.subsysid=? AND t2.envid=? AND t1.bssid=t2.bssid AND t1.subsysid=t2.subsysid AND t2.webconsolesn=t1.webconsolesn";
					ps = conn.prepareStatement(strSQL);
					ps.setString(1, bssId);
					ps.setString(2, subSysId);
					ps.setString(3, envId);
					rs = ps.executeQuery();
					rs.next();
					vmhs.setFilePath( rs.getString("webconsoleip") );		//Web的情境下，借用 FilePath來做儲存 Web Console的位置
					
					for (int i=0; i < hsLists.length ; i++ ) {																					//hsList中是所有要去的資源群組
						ValHisStruc v1 = new ValHisStruc();																					//記錄某個資源群組																		
						v1.setSquadId( hsLists[i] );	//資源群組ID
						ArrayList<S5> strcs = new ArrayList<S5>(); 
						S5 strc = new S5();
						strc.setA( vmhs.getFilePath() );		//主機名稱
						strcs.add( strc );
						v1.setLaos(strcs);		//這個資源群組，要被部署去這些主機 ( 那些主機的資訊是從 "ValidateHost" 提供的 )
						v1s.add(v1);
					}
					vmhs.setVhses( v1s );	//所有要去的資源群組
					
					vmWebs.add( vmhs );
					vmhses.setWeb( vmWebs );
				}
			} else if ( "Manual".equalsIgnoreCase(type) ) {	//手動式部署
				if ( filePortfolio==1 || filePortfolio==2 ) {
					JsonReader reader = new JsonReader( new StringReader( request.getParameter("tar") ) );
					reader.beginArray();
					while (reader.hasNext()) {
						reader.beginObject();
						ValMultiHisStruc vmhs = new ValMultiHisStruc();			//檔案
						ArrayList<ValHisStruc> v1s = new ArrayList<ValHisStruc>();
						
						reader.nextName();		//檔名
						vmhs.setFileName( reader.nextString() );
						
						reader.nextName();	//版本
						vmhs.setFileVer( Integer.parseInt( reader.nextString() ) );

						reader.nextName();		//執行方式
						isnonweb = "non-web".equalsIgnoreCase( reader.nextString() );
						
						reader.nextName();	//延伸路徑
						vmhs.setFilePath( reader.nextString() );

						reader.nextName();	//SVN路徑
						reader.nextString();
						
						reader.nextName();	//資源群組
						reader.beginArray();
						while (reader.hasNext()) {
							reader.beginObject();
							ValHisStruc v1 = new ValHisStruc();						//資源群組
							reader.nextName();		//資源群組ID
							String squadId = reader.nextString(); 
							v1.setSquadId( squadId );																		
							reader.nextName();						//資源群組名稱
							v1.setSquadName( reader.nextString() );
							reader.nextName();						
							v1.setSquadPath( reader.nextString() );	//資源群組路徑
							reader.endObject();

							ArrayList<S5> strcs = new ArrayList<S5>();			//那個資源群組中的所有主機
							
							strSQL ="SELECT t2.hostname,t1.ip,t1.port FROM mpsquadhosts t1, hosts t2 WHERE t1.ip=t2.ip AND t1.bssid=t2.bssid AND t1.port=t2.port AND t1.subsysid=t2.subsysid AND t1.bssid=? AND t1.subsysid=? AND t1.envid=? AND t1.squadid=?";
							errPars =
									wid.toJ("bssid", bssId )+
									wid.toJ("bssid", subSysId )+
									wid.toJ("bssid", envId )+
									wid.toJend("squadid", squadId );
							
							ps = conn.prepareStatement(strSQL);
							ps.setString(1, bssId);
							ps.setString(2, subSysId);
							ps.setString(3, envId);
							ps.setString(4, squadId );
							rs3 = ps.executeQuery();
							
							while ( rs3.next() ) {
								S5 strc = new S5();												//主機
								strc.setA( wid.DB2UI( rs3.getString("hostname"),conn.getMetaData().getDatabaseProductName() ) );	//主機名稱
								strc.setB( rs3.getString("ip") );										//主機IP
								strc.setC( String.valueOf( rs3.getInt("port") ) );			//主機Port
								strcs.add(strc);	
							}
							v1.setLaos(strcs);
							v1s.add(v1);
						}
						reader.endArray();
						reader.endObject();
						
						vmhs.setVhses(v1s);
						
						if ( isnonweb ) {
							vmNonWebs.add(vmhs);	
						} else {
							vmWebs.add(vmhs);
						}
					}
					reader.endArray();
					reader.close();
				}
			} else {		//批次部署
				int deplmainSN = Integer.parseInt(request.getParameter("deplmainSN"));
				errPars =
						wid.toJend("deplmainSN", deplmainSN );
				if ( filePortfolio==1 || filePortfolio==2 ) {	//處理所有nonWeb
					//strSQL = "SELECT t1.depldetailid,t1.localFile,t1.depPath,t1.localVer,t1.isnonweb FROM depldetail t1,depdetail t2 WHERE t2.deplmainSN=t1.deplmainSN AND t2.depldetailid=t1.depldetailid AND t2.depmainSN=? AND isnonweb='t'";
					strSQL = "SELECT t1.depldetailid,t1.localFile,t1.depPath,t1.localVer,t1.isnonweb FROM depldetail t1 WHERE t1.deplmainSN=? AND isnonweb='t'";
					ps = conn.prepareStatement(strSQL);
					ps.setInt(1, deplmainSN );
					rs = ps.executeQuery();
					while ( rs.next() ) {
						ValMultiHisStruc vmhs = new ValMultiHisStruc();							//檔案
						vmhs.setFileName( rs.getString("localFile") );									//檔案名稱
						vmhs.setFilePath( rs.getString("depPath") );									//檔案路徑
						vmhs.setFileVer( rs.getInt("localVer") );											//檔案版次
						ArrayList<ValHisStruc> v1s = new ArrayList<ValHisStruc>();		//記錄某個資源群組
						vmhs.setDeplmainSN(deplmainSN);												//部署計畫ID 	(前端產生分頁時使用)
						vmhs.setDepldetailid(rs.getInt("depldetailid"));								//部署計畫細項	(前端產生分頁時使用)
						
						strSQL = "SELECT t2.squadpath,t2.squadname,t2.squadid  FROM mpdeplunitsqu t1,squad t2 WHERE t1.bssid=t2.bssid AND t1.squadid=t2.squadid AND t1.subsysid=t2.subsysid AND t1.bssid=? AND t1.subsysid=? AND t1.deplmainSN=? AND t1.depldetailid=?";
						errPars =
								wid.toJ("bssid", bssId)+
								wid.toJ("subsysid", subSysId)+
								wid.toJ("deplmainSN", deplmainSN)+
								wid.toJend("depldetailid", rs.getInt("depldetailid") );
								
						ps = conn.prepareStatement(strSQL);
						ps.setString(1, bssId);
						ps.setString(2, subSysId);
						ps.setInt(3, deplmainSN );
						ps.setInt(4, rs.getInt("depldetailid") );
						rs2 = ps.executeQuery();
						
						while ( rs2.next() ) {
							ValHisStruc v1 = new ValHisStruc();																						//資源群組
							v1.setSquadId(rs2.getString("squadid"));																				//資源群組ID
							v1.setSquadName( wid.DB2UI( rs2.getString("squadname"),conn.getMetaData().getDatabaseProductName() ) );										//資源群組名稱
							v1.setSquadPath(rs2.getString("squadpath")==null?"":rs2.getString("squadpath"));	//資源群組路徑

							ArrayList<S5> strcs = new ArrayList<S5>();															//那個資源群組中的所有主機
							
							strSQL ="SELECT t2.hostname,t1.ip,t1.port FROM mpsquadhosts t1, hosts t2 WHERE t1.ip=t2.ip AND t1.bssid=t2.bssid AND t1.port=t2.port AND t1.subsysid=t2.subsysid AND t1.bssid=? AND t1.subsysid=? AND t1.envid=? AND t1.squadid=?";
							errPars =
									wid.toJ("bssid", bssId)+
									wid.toJ("subsysid", subSysId)+
									wid.toJ("envid", envId)+
									wid.toJend("squadid", rs2.getString("squadid") );
							
							ps = conn.prepareStatement(strSQL);
							ps.setString(1, bssId);
							ps.setString(2, subSysId);
							ps.setString(3, envId);
							ps.setString(4, rs2.getString("squadid") );
							rs3 = ps.executeQuery();
							
							while ( rs3.next() ) {
								S5 strc = new S5();												//主機
								strc.setA( wid.DB2UI( rs3.getString("hostname"),conn.getMetaData().getDatabaseProductName() ) );						//主機名稱
								strc.setB( rs3.getString("ip") );										//主機IP
								strc.setC( String.valueOf( rs3.getInt("port") ) );		//主機Port
								strcs.add(strc);	
							}
							v1.setLaos(strcs);
							v1s.add(v1);
						}
						vmhs.setVhses(v1s);
						vmNonWebs.add(vmhs);					
					}	
				} 
				if ( filePortfolio==1 || filePortfolio==3 ) {
					//strSQL = "SELECT t2.webconsoleip,t1.depldetailid,t1.localFile,t1.localVer,t4.squadid FROM depldetail t1,wcl t2,env t3,mpdeplunitsqu t4,depdetail t5 WHERE t5.deplmainSN=t1.deplmainSN AND t5.depldetailid=t1.depldetailid AND t4.deplmainSN=t1.deplmainSN AND t4.depldetailid=t1.depldetailid AND t4.bssid=t3.bssid AND t4.subsysid=t3.subsysid AND t2.bssid=t3.bssid AND  t3.webconsolesn=t2.webconsolesn AND t5.depmainSN=? AND t1.isnonweb='f' AND  t3.bssid=? AND t3.subsysid=? AND t3.envid=?" ;
					strSQL = "SELECT t2.webconsoleip,t1.depldetailid,t1.localFile,t1.localVer,t4.squadid FROM depldetail t1,wcl t2,env t3,mpdeplunitsqu t4 WHERE t4.deplmainSN=t1.deplmainSN AND t4.depldetailid=t1.depldetailid AND t4.bssid=t3.bssid AND t4.subsysid=t3.subsysid AND t2.bssid=t3.bssid AND  t3.webconsolesn=t2.webconsolesn AND t1.deplmainSN=? AND t1.isnonweb='f' AND  t3.bssid=? AND t3.subsysid=? AND t3.envid=?" ;
					
					ps = conn.prepareStatement(strSQL);
					ps.setInt(1, deplmainSN );
					ps.setString(2, bssId );
					ps.setString(3, subSysId );
					ps.setString(4, envId );
					rs = ps.executeQuery();

					while ( rs.next() ) {
						ValMultiHisStruc vmhs = new ValMultiHisStruc();							//檔案
						vmhs.setFileName( wid.DB2UI( rs.getString("localFile"),conn.getMetaData().getDatabaseProductName() ) );			//檔案名稱
						vmhs.setFileVer( rs.getInt("localVer") );											//檔案版次
						vmhs.setFilePath( rs.getString("webconsoleip") );							//借用 File Path來放 Web Console IP
						ArrayList<ValHisStruc> v1s = new ArrayList<ValHisStruc>();		//要去那些資源群組
						vmhs.setDeplmainSN(deplmainSN);												//部署計畫ID 	(前端產生分頁時使用)
						vmhs.setDepldetailid(rs.getInt("depldetailid"));								//部署計畫細項	(前端產生分頁時使用)
						
						/*	查詢這個細項要去的Squad 的資訊 	*/
						strSQL = "SELECT t2.squadname,t2.squadid,t4.modulepath FROM mpdeplunitsqu t1,squad t2,env t3, wcl t4 WHERE t1.bssid=t2.bssid AND  t1.bssid=t3.bssid AND t1.subsysid=t2.subsysid AND t1.subsysid=t3.subsysid AND t3.webconsolesn=t4.webconsolesn AND t1.squadid=t2.squadid AND t1.bssid=? AND t1.subsysid=? AND t1.deplmainSN=? AND t1.depldetailid=?  AND t3.envId=?";
						errPars =
								wid.toJ("bssid", bssId)+
								wid.toJ("subsysid", subSysId)+
								wid.toJ("deplmainSN", deplmainSN)+
								wid.toJ("depldetailid", rs.getInt("depldetailid"))+
								wid.toJend("envid", envId );
						
						ps = conn.prepareStatement(strSQL);
						ps.setString(1, bssId);
						ps.setString(2, subSysId);
						ps.setInt(3, deplmainSN );
						ps.setInt(4, rs.getInt("depldetailid") );
						ps.setString(5, envId);
						rs2 = ps.executeQuery();
						
						while ( rs2.next() ) {
							ValHisStruc v1 = new ValHisStruc();																								//資源群組
							v1.setSquadId( rs2.getString("squadid") );																					//資源群組ID
							v1.setSquadName( wid.DB2UI( rs2.getString("squadname"),conn.getMetaData().getDatabaseProductName() ) );												//資源群組名稱
							v1.setSquadPath( rs2.getString("modulepath")==null?"":rs2.getString("modulepath"));	//Web Console 上要放的路徑路徑
							ArrayList<S5> strcs = new ArrayList<S5>(); 
							S5 strc = new S5();
							strc.setA( vmhs.getFilePath() );		//主機名稱
							strcs.add( strc );
							v1.setLaos(strcs);		//這個資源群組，要被部署去這些主機 ( 那些主機的資訊是從 "ValidateHost" 提供的 )
							v1s.add(v1);
						}
						vmhs.setVhses(v1s);
						vmWebs.add(vmhs);
					}	
				}
				
				vmhses.setNonWeb(vmNonWebs);
				vmhses.setWeb(vmWebs);
			}
			
			
			vmhses.setNonWebOK( true );	//執行比對前先預設nonWeb的部份沒問題
			vmhses.setWebOK( true );			//執行比對前先預設Web的部份沒問題
			
			
			/*****************	以下開始比對部署過的記錄	*****************/
			
			// 比對 Non Web的部份
			if ( vmNonWebs.size() > 0 ) {
				/**
				 * 	查詢每台主機的部署記錄
				 * 	若存在「更新版次」的記錄
				 * 	記錄  版次、執行人員、執行日期
				 ** 		*/
				
				/*	取得當前資源群組路徑，以筆對暨有的部署記錄	
				 * 
				 * 	現有的：
				 * 	主機					v
				 * 	資源群組路徑 v
				 * 	延伸路徑			
				 * 	檔案名稱
				 * 	版次
				 * */
				strSQL = "SELECT t6.squadname,t5.username,t3.eTime,t1.localVer FROM depldetail t1,depdetail t2,itemdeplog t3,depmain t4,personnel t5,squad t6 WHERE t6.bssid=t3.bssid AND t6.subsysid=t3.subsysid AND t6.squadid=t3.squadid AND t5.userid=t4.creator AND t4.depmainSN=t2.depmainSN AND t3.depdetailid=t2.depdetailid AND t3.depmainSN=t2.depmainSN AND t1.depldetailid=t2.depldetailid AND t1.deplmainSN=t2.deplmainSN AND t3.bssid=? AND t3.subsysid=? AND t3.envid=? AND t1.depPath=? AND t1.localFile=? AND t3.squadid=? AND t1.isnonweb='t' AND t3.squadpath=? AND t3.ip=? ORDER BY t3.eTime DESC";
				
				/**	每個檔案，每個資源群組，一台台主機去找	*/
				ps = conn.prepareStatement(strSQL);
				ps.setString(1, bssId);
				ps.setString(2, subSysId);
				ps.setString(3, envId);
				
				for (int m=0; m < vmhses.getNonWeb().size(); m++ ) {		/**	m代表不同的檔案	*/
					ps.setString(4, vmhses.getNonWeb().get(m).getFilePath() );
					ps.setString(5, vmhses.getNonWeb().get(m).getFileName() );
					
					for (int i=0; i < vmhses.getNonWeb().get(m).getVhses().size(); i++ ) {		/**	i代表不同的資源群組	*/
						ps.setString(6, vmhses.getNonWeb().get(m).getVhses().get(i).getSquadId() );
						ps.setString(7, vmhses.getNonWeb().get(m).getVhses().get(i).getSquadPath() );
						for ( int j=0; j< vmhses.getNonWeb().get(m).getVhses().get(i).getLaos().size(); j++ ) {		/**	j代表不同的主機	*/
							ps.setString(8, vmhses.getNonWeb().get(m).getVhses().get(i).getLaos().get(j).getB() );
							errPars =
									wid.toJ("bssid", bssId ) +
									wid.toJ("subsysid", subSysId) +
									wid.toJ("envid", envId) +
									wid.toJ("depPath", vmhses.getNonWeb().get(m).getFilePath() ) +
									wid.toJ("localFile", vmhses.getNonWeb().get(m).getFileName() ) +
									wid.toJ("squadid", vmhses.getNonWeb().get(m).getVhses().get(i).getSquadId() ) +
									wid.toJ("squadpath", vmhses.getNonWeb().get(m).getVhses().get(i).getSquadPath() ) +
									wid.toJend("ip", vmhses.getNonWeb().get(m).getVhses().get(i).getLaos().get(j).getB()  );
							
							rs = ps.executeQuery();
							if ( rs.next() ) {		//表示存在更新的部署記錄，將資料寫入	用 if，不是用while，只取第一筆，代表是最後部署的，表示目前在主機上的檔案資訊
								if ( vmhses.getNonWeb().get(m).getFileVer()< rs.getInt("localVer") ) {
									vmhses.setNonWebOK( false );	//表示nonWeb的部份存在問題
									vmhses.getNonWeb().get(m).getVhses().get(i).setSquadName( wid.DB2UI( rs.getString("squadname"),conn.getMetaData().getDatabaseProductName() ) );	//資源群組的名字
									vmhses.getNonWeb().get(m).getVhses().get(i).setSquadResult("FAIL");												//表示此 資源群組中存在失敗的主機，供前端UI解析
									/*	這是目前在那台機器上的版本	*/
									vmhses.getNonWeb().get(m).getVhses().get(i).getLaos().get(j).setC( wid.DB2UI( rs.getString("username"),conn.getMetaData().getDatabaseProductName() ) );													//借用 C 來放置	部署人員
									vmhses.getNonWeb().get(m).getVhses().get(i).getLaos().get(j).setD( wid.changeTimeFormat( rs.getTimestamp("eTime") ));							//借用 D 來放置 部署時間
									vmhses.getNonWeb().get(m).getVhses().get(i).getLaos().get(j).setE( String.valueOf( rs.getInt("localVer") ) );														//借用 E 來放置 部署版本
									/*	所以只要 E 這個欄位是空的(因為先前沒放)，就表示這台機器是正常的		*/
									vmhses.getNonWeb().get(m).setFileResult("FAIL");												//表示此 資源群組中存在失敗的主機，供前端UI解析
									if ( wid.isDebug ) {
										System.out.println("bssId："+bssId);
										System.out.println("subSysId："+subSysId);
										System.out.println("envId："+envId);
										System.out.println("depPath："+vmNonWebs.get(m).getFilePath());
										System.out.println("fileName："+vmNonWebs.get(m).getFileName());
										System.out.println("ver："+vmNonWebs.get(m).getFileVer());
										System.out.println("squadId："+vmNonWebs.get(m).getVhses().get(i).getSquadId());
										System.out.println("squadPath："+vmNonWebs.get(m).getVhses().get(i).getSquadPath());
										System.out.println("ip："+vmNonWebs.get(m).getVhses().get(i).getLaos().get(j).getB());	
									}
								}
							}
						}
					}
				}
			}
			
			if ( vmWebs.size() > 0 ) {

				if ( conn.getMetaData().getDatabaseProductName().contains( "MySQL" ) ) {
					strSQL = "SELECT t6.squadname,t5.username,t3.eTime,t1.localVer FROM depldetail t1,depdetail t2,itemdeplog t3,depmain t4,personnel t5,squad t6 WHERE t6.bssid=t3.bssid AND t6.subsysid=t3.subsysid AND t6.squadid=t3.squadid AND t5.userid=t4.creator AND t4.depmainSN=t2.depmainSN AND t3.depdetailid=t2.depdetailid AND t3.depmainSN=t2.depmainSN AND t1.depldetailid=t2.depldetailid AND t1.deplmainSN=t2.deplmainSN AND t3.bssid=? AND t3.subsysid=? AND t3.envid=? AND t1.depPath=? AND t1.localFile=? AND t3.squadid=? AND t1.isnonweb='f' AND t3.squadpath=? AND t3.ip=? ORDER BY t3.eTime DESC";	
				} else if ( conn.getMetaData().getDatabaseProductName().contains( "Informix" ) ) {
					strSQL = "SELECT t7.webconsoleip,t6.squadname,t5.username,t3.eTime,t1.localVer FROM depldetail t1,depdetail t2,itemdeplog t3,depmain t4,squad t6,wcl t7, OUTER personnel t5 WHERE t6.bssid=t3.bssid AND t6.subsysid=t3.subsysid AND t6.squadid=t3.squadid AND t5.userid=t4.creator AND t4.depmainSN=t2.depmainSN AND t3.depdetailid=t2.depdetailid AND t3.depmainSN=t2.depmainSN AND t1.depldetailid=t2.depldetailid AND t1.deplmainSN=t2.deplmainSN AND t3.bssid=? AND t3.subsysid=? AND t3.envid=? AND t1.localFile=? AND t3.ip=? AND t3.squadid=? AND t1.isnonweb='f' AND t3.ip=t7.webconsoleip ORDER BY t3.eTime DESC";
				}
				
				/**	每個檔案，每個資源群組，一台台主機去找	*/
				ps = conn.prepareStatement(strSQL);
				ps.setString(1, bssId);
				ps.setString(2, subSysId);
				ps.setString(3, envId);
				
				for (int m=0; m < vmWebs.size(); m++ ) {		/**	m代表不同的檔案	*/
					ps.setString(4, vmWebs.get(m).getFileName() );
					ps.setString(5, vmWebs.get(m).getFilePath() );		//借用 FilePath來存放 Web Console的位置
					
					for (int i=0; i < vmWebs.get(m).getVhses().size(); i++ ) {		/**	i代表不同的資源群組	*/
						ps.setString(6, vmWebs.get(m).getVhses().get(i).getSquadId() );
						for ( int j=0; j< vmWebs.get(m).getVhses().get(i).getLaos().size(); j++ ) {		/**	j代表不同的主機	*/
							errPars =
									wid.toJ("bssid", bssId ) +
									wid.toJ("subsysid", subSysId) +
									wid.toJ("envid", envId) +
									wid.toJ("localFile", wid.UI2DB( vmWebs.get(m).getFileName(),conn.getMetaData().getDatabaseProductName() ) ) +
									wid.toJ("webconsoleip", vmWebs.get(m).getFilePath() ) +
									wid.toJend("squadid", vmWebs.get(m).getVhses().get(i).getSquadId() );
							
							rs = ps.executeQuery();
							if ( rs.next() ) {		//表示存在更新的部署記錄，將資料寫入	用 if，不是用while，只取第一筆，代表是最後部署的，表示目前在主機上的檔案資訊
								
								if ( vmhses.getWeb().get(m).getFileVer()< rs.getInt("localVer") ) {
									vmhses.setWebOK( false );	//表示Web的部份存在問題
									vmhses.getWeb().get(m).getVhses().get(i).setSquadName( wid.DB2UI( rs.getString("squadname"),conn.getMetaData().getDatabaseProductName() ) );	//資源群組的名字
									vmhses.getWeb().get(m).getVhses().get(i).setSquadResult("FAIL");												//表示此 資源群組中存在失敗的主機，供前端UI解析
									/*	這是目前在那台機器上的版本	*/
									vmhses.getWeb().get(m).getVhses().get(i).getLaos().get(j).setC( wid.DB2UI( rs.getString("username"),conn.getMetaData().getDatabaseProductName() ) );													//借用 C 來放置	部署人員
									vmhses.getWeb().get(m).getVhses().get(i).getLaos().get(j).setD( wid.changeTimeFormat( rs.getTimestamp("eTime") ));							//借用 D 來放置 部署時間
									vmhses.getWeb().get(m).getVhses().get(i).getLaos().get(j).setE( String.valueOf( rs.getInt("localVer") ) );														//借用 E 來放置 部署版本
									/*	所以只要 E 這個欄位是空的(因為先前沒放)，就表示這台機器是正常的		*/
									vmhses.getWeb().get(m).setFileResult("FAIL");												//表示此 資源群組中存在失敗的主機，供前端UI解析
								}
							}
						}
					}
				}
			}
			
			if ( "Manual".equalsIgnoreCase(type) ) {	//手動式部署
				for (int i =0; i< vmNonWebs.size();i++) {
					vmNonWebs.get(i).setFileName( vmNonWebs.get(i).getFileName().replace(".", wid.splitTokenONE) );
				}
			}
		} catch ( UnsupportedEncodingException e) {
			wid.kem(conn, ps, e, wid.toEType("UnsupportedEncodingException")+errPars, wid.isPrint2Console );
		}  catch ( SQLException e) {
			wid.kem(conn, ps, e, wid.toEType("SQLException")+errPars, wid.isPrint2Console );
		}  catch ( Exception e) {
			wid.kem(conn, ps, e, wid.toEType("Exception")+errPars, wid.isPrint2Console );
		} finally {
			try {
				if ( rs != null ) { rs.close(); }
				if ( rs2 != null ) { rs2.close(); }
				if ( rs3 != null ) { rs3.close(); }
				if ( ps!= null ) { ps.close(); }
				if ( conn!= null ) { conn.close(); }
			} catch ( SQLException e ) {
				System.out.println( wid.errProcess(e) );
			}
		}
		
		response.getWriter().write(new Gson().toJson(vmhses));
	}
}